using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Text;

using System.Data.OracleClient;
namespace SqlToolkit.Oracle.Schema
{
    public class SqlSchema
    {
        /// <summary>
        /// 
        /// </summary>
        /// <param name="conn"></param>
        /// <returns></returns>
        public static SqlDbTableCollection AllTables(OracleDB conn)
        {
            SqlDbTableCollection tbls = null;
            Resx.Reader rx = new Resx.Reader();
            string sql = string.Empty;
            System.Data.IDataReader r = null;

            try
            {
                sql = rx.ResourceString("SqlToolkit.Oracle.Schema.Resx.USER_VIEWS.DB.TABLES.DbTables.sql");

                r = conn.DbAccessor.ExecuteSqlText.ExecuteReader(sql);

                tbls = SqlDbTable.BuildCollection(conn, r);
            }
            catch (Exception ex)
            {
                throw new Exception("There was an error while attempting to load table data.", ex);
            }
            finally
            {
                if (null != r)
                {
                    r.Close();
                    r.Dispose();
                    r = null;
                }
            }

            return tbls;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="table"></param>
        /// <returns></returns>
        public static SqlDbTable Table(OracleDB conn, string table)
        {
            //SELECT USER_TABLES.TABLE_NAME FROM USER_TABLES ORDER BY USER_TABLES.TABLE_NAME
            SqlDbTable tbl = null;
            string sql = string.Empty;
            System.Data.IDataReader r = null;

            try
            {
                //SELECT USER_TABLES.TABLE_NAME FROM USER_TABLES ORDER BY USER_TABLES.TABLE_NAME
                sql = "SELECT USER_TABLES.TABLE_NAME FROM USER_TABLES WHERE USER_TABLES.TABLE_NAME = '" + table + "'";

                r = conn.DbAccessor.ExecuteSqlText.ExecuteReader(sql);

                r.Read();
                tbl = SqlDbTable.Build(conn, r);
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex.Message);
                //throw new Exception("There was an error while attempting to load table data.", ex);
            }
            finally
            {
                if (null != r)
                {
                    r.Close();
                    r.Dispose();
                    r = null;
                }
            }

            return tbl;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="conn"></param>
        /// <returns></returns>
        public static SqlDbViewCollection AllViews(OracleDB conn)
        {
            SqlDbViewCollection tbls = null;
            Resx.Reader rx = new Resx.Reader();
            string sql = string.Empty;
            System.Data.IDataReader r = null;

            try
            {
                sql = rx.ResourceString("SqlToolkit.Oracle.Schema.Resx.USER_VIEWS.DB.VIEWS.DbViews.sql");

                r = conn.DbAccessor.ExecuteSqlText.ExecuteReader(sql);

                tbls = SqlDbView.BuildCollection(conn, r);
            }
            catch (Exception ex)
            {
                throw new Exception("There was an error while attempting to load table data.", ex);
            }
            finally
            {
                if (null != r)
                {
                    r.Close();
                    r.Dispose();
                    r = null;
                }
            }

            return tbls;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public static SqlDbTableFieldCollection TableColumns(OracleDB conn, string tableName)
        {
            SqlDbTableFieldCollection flds = null;
            Resx.Reader rx = new Resx.Reader();
            string sql = string.Empty;
            System.Data.IDataReader r = null;

            try
            {
                sql = rx.ResourceString("SqlToolkit.Oracle.Schema.Resx.USER_VIEWS.DB.TABLES.COLUMNS.DbColumns.sql");
                sql = sql.Replace("@TableName", tableName);

                r = conn.DbAccessor.ExecuteSqlText.ExecuteReader(sql);

                flds = SqlDbTableField.BuildCollection(conn, r);
            }
            catch (Exception ex)
            {
                throw new Exception("There was an error while attempting to load table column data.", ex);
            }
            finally
            {
                if (null != r)
                {
                    r.Close();
                    r.Dispose();
                    r = null;
                }
            }

            return flds;
        }
        public static SqlDbTableFieldCollection TableColumns(OracleDB conn, string tableName, string schema)
        {
            SqlDbTableFieldCollection flds = null;
            string sql = string.Empty;
            System.Data.IDataReader r = null;

            try
            {
                sql = "SELECT ALL_TAB_COLUMNS.TABLE_NAME, ALL_TAB_COLUMNS.COLUMN_NAME, ALL_TAB_COLUMNS.DATA_TYPE, ALL_TAB_COLUMNS.DATA_DEFAULT,ALL_TAB_COLUMNS.CHAR_LENGTH, ALL_TAB_COLUMNS.COLUMN_ID, ALL_TAB_COLUMNS.DATA_LENGTH, ALL_TAB_COLUMNS.DATA_PRECISION,ALL_TAB_COLUMNS.OWNER FROM ALL_TAB_COLUMNS WHERE ALL_TAB_COLUMNS.OWNER = :own AND ALL_TAB_COLUMNS.TABLE_NAME = :tbl ORDER BY ALL_TAB_COLUMNS.COLUMN_ID";
                
                OracleParameter[] p = new OracleParameter[2];
                p[0] = new OracleParameter();
                p[1] = new OracleParameter();

                p[0].ParameterName = "own";
                p[0].Value = schema;

                p[1].ParameterName = "tbl";
                p[1].Value = tableName;

                r = conn.DbAccessor.ExecuteSqlText.ExecuteReader(sql,p);

                flds = SqlDbTableField.BuildCollection(conn, r);
            }
            catch (Exception ex)
            {
                throw new Exception("There was an error while attempting to load table column data.", ex);
            }
            finally
            {
                if (null != r)
                {
                    r.Close();
                    r.Dispose();
                    r = null;
                }
            }

            return flds;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="viewName"></param>
        /// <returns></returns>
        public static SqlDbViewFieldCollection ViewColumns(OracleDB conn, string viewName)
        {
            SqlDbViewFieldCollection flds = null;
            Resx.Reader rx = new Resx.Reader();
            string sql = string.Empty;
            System.Data.IDataReader r = null;

            try
            {
                sql = rx.ResourceString("SqlToolkit.Oracle.Schema.Resx.USER_VIEWS.DB.TABLES.COLUMNS.DbColumns.sql");
                sql = sql.Replace("@TableName", viewName);

                r = conn.DbAccessor.ExecuteSqlText.ExecuteReader(sql);

                flds = SqlDbViewField.BuildCollection(conn, r);
            }
            catch (Exception ex)
            {
                throw new Exception("There was an error while attempting to load table column data.", ex);
            }
            finally
            {
                if (null != r)
                {
                    r.Close();
                    r.Dispose();
                    r = null;
                }
            }

            return flds;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="conn"></param>
        /// <returns></returns>
        public static SqlDbConstraintCollection AllConstraints(OracleDB conn)
        {
            SqlDbConstraintCollection cns = null;
            Resx.Reader rx = new Resx.Reader();
            string sql = string.Empty;
            System.Data.IDataReader r = null;

            try
            {
                sql = rx.ResourceString("SqlToolkit.Oracle.Schema.Resx.USER_VIEWS.DB.DbConstraints.sql");

                r = conn.DbAccessor.ExecuteSqlText.ExecuteReader(sql);
                cns = SqlDbConstraint.BuildCollection(conn, r);
            }
            catch (Exception ex)
            {
                throw new Exception("There was an error while attempting to load constraint data.", ex);
            }
            finally
            {
                if (null != r)
                {
                    r.Close();
                    r.Dispose();
                    r = null;
                }
            }

            return cns;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public static SqlDbConstraintCollection TableConstraints(OracleDB conn, string tableName)
        {
            SqlDbConstraintCollection cns = null;
            Resx.Reader rx = new Resx.Reader();
            string sql = string.Empty;
            System.Data.IDataReader r = null;

            try
            {
                sql = rx.ResourceString("SqlToolkit.Oracle.Schema.Resx.USER_VIEWS.DB.TABLES.DbTableConstraints.sql");
                sql = sql.Replace("@TableName", tableName);

                r = conn.DbAccessor.ExecuteSqlText.ExecuteReader(sql);
                cns = SqlDbConstraint.BuildCollection(conn, r);
            }
            catch (Exception ex)
            {
                throw new Exception("There was an error while attempting to load constraint data.", ex);
            }
            finally
            {
                if (null != r)
                {
                    r.Close();
                    r.Dispose();
                    r = null;
                }
            }

            return cns;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="conn"></param>
        /// <returns></returns>
        public static SqlDbProcedureCollection DbProcedures(OracleDB conn)
        {
            //OBJECT_NAME,PROCEDURE_NAME,AGGREGATE,PIPELINED,IMPLTYPEOWNER,IMPLTYPENAME,PARALLEL,INTERFACE,DETERMINISTIC,AUTHID
            string runSql = "SELECT OBJECT_NAME,PROCEDURE_NAME,AGGREGATE,PIPELINED,IMPLTYPEOWNER,IMPLTYPENAME,PARALLEL,INTERFACE,DETERMINISTIC,AUTHID FROM USER_PROCEDURES WHERE OBJECT_TYPE = 'PROCEDURE' ORDER BY PROCEDURE_NAME";

            System.Data.IDataReader r = null;

            SqlDbProcedureCollection ret = null;
            try
            {
                r = conn.DbAccessor.ExecuteSqlText.ExecuteReader(runSql);

                if (null != r)
                {
                    ret = SqlDbProcedure.BuildCollection(r);
                }

            }
            catch (Exception ex)
            {
                throw new Exception("There was an error while attempting to load procedure data.", ex);
            }
            finally
            {
                if (null != r)
                {
                    r.Close();
                    r = null;
                }
            }

            return ret;

        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="conn"></param>
        /// <returns></returns>
        public static SqlDbProcedureCollection DbFunctions(OracleDB conn)
        {
            string runSql = "SELECT OBJECT_NAME,PROCEDURE_NAME,AGGREGATE,PIPELINED,IMPLTYPEOWNER,IMPLTYPENAME,PARALLEL,INTERFACE,DETERMINISTIC,AUTHID FROM USER_PROCEDURES WHERE OBJECT_TYPE = 'FUNCTION' ORDER BY PROCEDURE_NAME";

            System.Data.IDataReader r = null;

            SqlDbProcedureCollection ret = null;
            try
            {
                r = conn.DbAccessor.ExecuteSqlText.ExecuteReader(runSql);

                if (null != r)
                {
                    ret = SqlDbProcedure.BuildCollection(r);
                }

            }
            catch (Exception ex)
            {
                throw new Exception("There was an error while attempting to load function data.", ex);
            }
            finally
            {
                if (null != r)
                {
                    r.Close();
                    r = null;
                }
            }

            return ret;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="conn"></param>
        /// <returns></returns>
        public static SqlDbProcedureCollection DbPackages(OracleDB conn)
        {
            string runSql = "SELECT OBJECT_NAME,PROCEDURE_NAME,AGGREGATE,PIPELINED,IMPLTYPEOWNER,IMPLTYPENAME,PARALLEL,INTERFACE,DETERMINISTIC,AUTHID FROM USER_PROCEDURES WHERE OBJECT_TYPE = 'PACKAGE' AND PROCEDURE_NAME IS NULL ORDER BY PROCEDURE_NAME";

            System.Data.IDataReader r = null;

            SqlDbProcedureCollection ret = null;
            try
            {
                r = conn.DbAccessor.ExecuteSqlText.ExecuteReader(runSql);

                if (null != r)
                {
                    ret = SqlDbProcedure.BuildCollection(r);
                }

            }
            catch (Exception ex)
            {
                throw new Exception("There was an error while attempting to load package data.", ex);
            }
            finally
            {
                if (null != r)
                {
                    r.Close();
                    r = null;
                }
            }

            return ret;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="conn"></param>
        /// <returns></returns>
        public static SqlDbSequenceCollection DbSequences(OracleDB conn)
        {
            string runSql = "SELECT SEQUENCE_NAME,MIN_VALUE,MAX_VALUE,INCREMENT_BY,CYCLE_FLAG,ORDER_FLAG,CACHE_SIZE,LAST_NUMBER FROM USER_SEQUENCES";

            System.Data.IDataReader r = null;

            SqlDbSequenceCollection ret = null;
            try
            {
                r = conn.DbAccessor.ExecuteSqlText.ExecuteReader(runSql);

                if (null != r)
                {
                    ret = SqlDbSequence.BuildCollection(r);
                }

            }
            catch (Exception ex)
            {
                throw new Exception("There was an error while attempting to load sequence data.", ex);
            }
            finally
            {
                if (null != r)
                {
                    r.Close();
                    r = null;
                }
            }

            return ret;
        }
        public static SqlDbSequence DbSequence(OracleDB conn, string name)
        {
            string runSql = "SELECT SEQUENCE_NAME,MIN_VALUE,MAX_VALUE,INCREMENT_BY,CYCLE_FLAG,ORDER_FLAG,CACHE_SIZE,LAST_NUMBER FROM USER_SEQUENCES WHERE SEQUENCE_NAME = :seqName";

            System.Data.IDataReader r = null;

            SqlDbSequence ret = null;
            try
            {
                System.Data.OracleClient.OracleParameter pName = conn.CreateParameter("seqName", name, ParameterDirection.Input, System.Data.OracleClient.OracleType.VarChar);
                r = conn.DbAccessor.ExecuteSqlText.ExecuteReader(runSql,pName);

                if (null != r)
                {
                    if(r.Read())ret = SqlDbSequence.Build(r);
                }

            }
            catch (Exception ex)
            {
                throw new Exception("There was an error while attempting to load sequence data.", ex);
            }
            finally
            {
                if (null != r)
                {
                    r.Close();
                    r = null;
                }
            }

            return ret;
        }
        public static string ProcedureSourceCode(OracleDB conn, string procedure)
        {

            IDataReader r = null;

            StringBuilder ret = new StringBuilder();

            try
            {
                r = conn.DbAccessor.ExecuteSqlText.ExecuteReader("SELECT TEXT FROM USER_SOURCE WHERE NAME = '" + procedure + "' AND TYPE = 'PROCEDURE'");
                while (r.Read())
                {
                    if (!r.IsDBNull(0)) ret.Append(r.GetValue(0).ToString());
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex.Message);
            }
            finally
            {
                if (null != r)
                {
                    r.Close();
                    r = null;
                }
            }


            string sRet = ret.ToString();
            return sRet;

        }

        public static string PackageSourceCode(OracleDB conn, string procedure)
        {

            IDataReader r = null;

            StringBuilder ret = new StringBuilder();

            try
            {
                r = conn.DbAccessor.ExecuteSqlText.ExecuteReader("SELECT TEXT FROM USER_SOURCE WHERE NAME = '" + procedure + "' AND TYPE = 'PACKAGE'");
                while (r.Read())
                {
                    if (!r.IsDBNull(0)) ret.Append(r.GetValue(0).ToString());
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex.Message);
            }
            finally
            {
                if (null != r)
                {
                    r.Close();
                    r = null;
                }
            }


            string sRet = ret.ToString();
            return sRet;

        }
        public static string PackageBodySourceCode(OracleDB conn, string procedure)
        {

            IDataReader r = null;

            StringBuilder ret = new StringBuilder();

            try
            {
                r = conn.DbAccessor.ExecuteSqlText.ExecuteReader("SELECT TEXT FROM USER_SOURCE WHERE NAME = '" + procedure + "' AND TYPE = 'PACKAGE BODY'");
                while (r.Read())
                {
                    if (!r.IsDBNull(0)) ret.Append(r.GetValue(0).ToString());
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex.Message);
            }
            finally
            {
                if (null != r)
                {
                    r.Close();
                    r = null;
                }
            }


            string sRet = ret.ToString();
            return sRet;

        }

        public static string FunctionSourceCode(OracleDB conn, string procedure)
        {

            IDataReader r = null;

            StringBuilder ret = new StringBuilder();

            try
            {
                r = conn.DbAccessor.ExecuteSqlText.ExecuteReader("SELECT TEXT FROM USER_SOURCE WHERE NAME = '" + procedure + "' AND TYPE = 'FUNCTION'");
                while (r.Read())
                {
                    if (!r.IsDBNull(0)) ret.Append(r.GetValue(0).ToString());
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex.Message);
            }
            finally
            {
                if (null != r)
                {
                    r.Close();
                    r = null;
                }
            }


            string sRet = ret.ToString();
            return sRet;

        }

        public static bool IsColumnUsed(OracleDB conn, string table, string column)
        {
            bool ret = true;

            try
            {
                string sql = "SELECT COUNT(0) FROM " + table + " WHERE " + column + " IS NOT NULL";
                object o = conn.DbAccessor.ExecuteSqlText.ExecuteScalar(sql);
                if (null != o)
                {
                    int i = Convert.ToInt32(o);
                    if (i == 0)
                        ret = false;
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex.Message);
            }

            return ret;
        }

        public static SqlDbTableFieldStatistics ColumnStatistics(OracleDB conn, string table, string column)
        {
            if (null == conn) return null;
            if (null == table || table.Trim().Length < 1) return null;
            if (null == column || column.Trim().Trim().Length < 1) return null;

            string sql = "SELECT TABLE_NAME,COLUMN_NAME, NUM_DISTINCT,LOW_VALUE,HIGH_VALUE,DENSITY, NUM_NULLS, NUM_BUCKETS, LAST_ANALYZED, SAMPLE_SIZE,GLOBAL_STATS,USER_STATS,AVG_COL_LEN FROM USER_TAB_COL_STATISTICS WHERE TABLE_NAME = :tableName AND COLUMN_NAME = :columnName";
            
            IDataReader r = null;
            SqlDbTableFieldStatistics ret = null;

            try
            {
                System.Data.OracleClient.OracleParameter tbl = conn.CreateParameter("tableName", table, ParameterDirection.Input, System.Data.OracleClient.OracleType.VarChar);
                System.Data.OracleClient.OracleParameter col = conn.CreateParameter("columnName", column, ParameterDirection.Input, System.Data.OracleClient.OracleType.VarChar);

                r = conn.DbAccessor.ExecuteSqlText.ExecuteReader(sql, new System.Data.OracleClient.OracleParameter[] {tbl,col });
                if (null != r)
                {
                    if (r.Read())
                    {
                        ret = SqlDbTableFieldStatistics.Build(r);
                    }
                }
            }
            catch (Exception readEx)
            {
                System.Diagnostics.Debug.WriteLine(readEx.Message);
            }
            finally
            {
                conn.CleanDataReader(r);
            }

            return ret;
        }

        public static SqlDbTableFieldStatisticsCollection TableColumnsStatistics(OracleDB conn, string table)
        {
            if (null == conn) return null;
            if (null == table || table.Trim().Length < 1) return null;

            string sql = "SELECT TABLE_NAME,COLUMN_NAME, NUM_DISTINCT,LOW_VALUE,HIGH_VALUE,DENSITY, NUM_NULLS, NUM_BUCKETS, LAST_ANALYZED, SAMPLE_SIZE,GLOBAL_STATS,USER_STATS,AVG_COL_LEN FROM USER_TAB_COL_STATISTICS WHERE TABLE_NAME = :tableName";

            IDataReader r = null;
            SqlDbTableFieldStatisticsCollection ret = null;

            try
            {
                System.Data.OracleClient.OracleParameter tbl = conn.CreateParameter("tableName", table, ParameterDirection.Input, System.Data.OracleClient.OracleType.VarChar);

                r = conn.DbAccessor.ExecuteSqlText.ExecuteReader(sql,tbl);
                if (null != r)
                {
                    ret = SqlDbTableFieldStatistics.BuildCollection(r);
                }
            }
            catch (Exception readEx)
            {
                System.Diagnostics.Debug.WriteLine(readEx.Message);
            }
            finally
            {
                conn.CleanDataReader(r);
            }

            if (null != ret && ret.Count < 1) ret = null;

            return ret;
        }

        public static List<string> EmptyColumns(OracleDB conn, string table)
        {
            if (null == conn || null == table) return null;
            if (table.Trim().Length < 1) return null;

            List<string> ret = new List<string>();
            IDataReader r = null;

            string sql = "SELECT tcs.TABLE_NAME, tcs.COLUMN_NAME FROM USER_TABLES tc, USER_TAB_COL_STATISTICS tcs WHERE tc.TABLE_NAME = tcs.TABLE_NAME AND tc.NUM_ROWS = tcs.NUM_NULLS AND tc.TABLE_NAME = :tableName";

            try
            {
                r = conn.DbAccessor.ExecuteSqlText.ExecuteReader(sql, conn.CreateParameter("tableName", table, ParameterDirection.Input, System.Data.OracleClient.OracleType.VarChar));
                if (null != r)
                {
                    while (r.Read())
                    {
                        if (!r.IsDBNull(0) && !r.IsDBNull(1))
                        {
                            ret.Add(Convert.ToString(r.GetValue(0)) + "." + Convert.ToString(r.GetValue(1)));
                        }
                    }
                }
            }
            catch (Exception readEx)
            {
                System.Diagnostics.Debug.WriteLine(readEx.Message);
            }
            finally
            {
                conn.CleanDataReader(r);
            }

            if (ret.Count < 1) ret = null;

            return ret;
        }
        public static List<string> EmptyColumns(OracleDB conn)
        {
            if (null == conn) return null;

            List<string> ret = new List<string>();
            IDataReader r = null;

            string sql = "SELECT tcs.TABLE_NAME, tcs.COLUMN_NAME FROM USER_TABLES tc, USER_TAB_COL_STATISTICS tcs WHERE tc.TABLE_NAME = tcs.TABLE_NAME AND tc.NUM_ROWS = tcs.NUM_NULLS";

            try
            {
                r = conn.DbAccessor.ExecuteSqlText.ExecuteReader(sql);
                if (null != r)
                {
                    while (r.Read())
                    {
                        if (!r.IsDBNull(0) && !r.IsDBNull(1))
                        {
                            ret.Add(Convert.ToString(r.GetValue(0)) + "." + Convert.ToString(r.GetValue(1)));
                        }
                    }
                }
            }
            catch (Exception readEx)
            {
                System.Diagnostics.Debug.WriteLine(readEx.Message);
            }
            finally
            {
                conn.CleanDataReader(r);
            }

            if (ret.Count < 1) ret = null;

            return ret;
        }
    }
}

